{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "inclusive-expression",
   "metadata": {},
   "source": [
    "## Example 2 - Plot PSDs for a station\n",
    "The intent of this series of Jupyter Notebooks is to demonstrate how metrics can be retrieved from an ISPAQ sqlite database and provide some ideas on how to use or plot those metrics.  \n",
    "\n",
    "This example plots station PSDs. It requires that we have the PSD values already calculated for the target for the requested\n",
    "days, and those values should live in the example database, named ispaq_example.db.  If you have not already, you can run this command in your ISPAQ conda environment\n",
    "to have the values generated for the target-days in this example (it will take several minutes to run):\n",
    "\n",
    "    python3 run_ispaq.py -M psd_corrected -S ANMO --starttime 2020-10-01 --endtime 2020-10-16 --output db --db_name ispaq_example.db\n",
    "\n",
    "This example will assume that the above command has already been run and the PSDs already exist.\n",
    "\n",
    "To begin, we need to import the necessary modules:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "trying-superior",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.dates import DateFormatter\n",
    "import matplotlib.dates as mdates\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "opposed-packet",
   "metadata": {},
   "source": [
    "Now we need to set some variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "peaceful-kinase",
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = 'PSD.png'\n",
    "db_name = '../ispaq_example.db'\n",
    "metric = 'psd_corrected'\n",
    "startDate = '2020-10-01'\n",
    "endDate = '2020-10-15'\n",
    "target = 'IU.ANMO.00.BH1.M'\n",
    "filename = f'example2_{target}_{startDate}_{endDate}_PSD.png'\n",
    "filename2 = f'example2_{target}_{startDate}_{endDate}_PSD_bw.png'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unable-texas",
   "metadata": {},
   "source": [
    "The first step is to create a query that will be used to retrieve the psds."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "elder-injection",
   "metadata": {},
   "outputs": [],
   "source": [
    "SQLcommand = \"SELECT * FROM \" + metric + \\\n",
    "                     \" WHERE start >= '\" + startDate + \"' \" \\\n",
    "                     \"and start < '\" + endDate + \"' \" \\\n",
    "                     \"and (target like '\" + target + \"');\"\n",
    "\n",
    "print('\\nThis is the query used to retrieve the PSDs from the database:')\n",
    "print(SQLcommand)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "recreational-trash",
   "metadata": {},
   "source": [
    "Create a connection to the database and run the query, loading it into a pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "relative-remains",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    conn = sqlite3.connect(db_name)\n",
    "    DF = pd.read_sql_query(SQLcommand, conn, parse_dates=['start','end'])\n",
    "    conn.close\n",
    "except:\n",
    "    print(f\"Unable to connect to or find the {metric} table in the database {db_name}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sustained-moses",
   "metadata": {},
   "source": [
    "At this point, we have created a query to retrieve the metrics from the SQLite database, used sqlite3 to connect to the database, retreieved the metrics, closed the connection, and then ensured that the start times are in a datetime format for plotting purposes.\n",
    "\n",
    "This is what the dataframe looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "otherwise-comparative",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(DF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "pending-canberra",
   "metadata": {},
   "source": [
    "The PSD plot will be power vs. frequency, so we are going to group them together by start time:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "noble-dietary",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF = DF[['frequency','power','start']]\n",
    "DFgrouped = DF.groupby(['start'])\n",
    "print(DFgrouped)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "flexible-polyester",
   "metadata": {},
   "source": [
    "We will take the information from the dataframe that we loaded and rearrange it for plotting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "boring-assets",
   "metadata": {},
   "outputs": [],
   "source": [
    "plotDF = pd.DataFrame()\n",
    "for name, group in DFgrouped:\n",
    "    tmpDF = pd.DataFrame()\n",
    "    tmpDF[name] = group['power']\n",
    "    tmpDF.set_axis(group['frequency'], axis='index', inplace=True)\n",
    "\n",
    "    plotDF = pd.concat([plotDF, tmpDF], axis=1, sort=False)\n",
    "\n",
    "print(\"\\nThis is the dataframe that will be used to plot:\")\n",
    "print(plotDF)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "martial-celtic",
   "metadata": {},
   "source": [
    "Now that we have the dataframe in this arrangement, we can start plotting it up.\n",
    "\n",
    "First, a colorful version of the plot:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "forty-bible",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ax = plotDF.plot(legend=False, alpha=1, title=f'{target}\\n{startDate} through {endDate}')\n",
    "ax.set_xscale('log')\n",
    "ax.invert_xaxis()\n",
    "ax.set_ylabel('power')\n",
    "plt.grid(True)\n",
    "plt.savefig(filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "upper-principal",
   "metadata": {},
   "source": [
    "Then, just for the sake of variety, a black and white version of the version of the plot:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "integrated-venue",
   "metadata": {},
   "outputs": [],
   "source": [
    "ax2 = plotDF.plot(legend=False, alpha=.02, color='k', title=f'{target}\\n{startDate} through {endDate}')\n",
    "ax2.set_xscale('log')\n",
    "ax2.invert_xaxis()\n",
    "ax2.set_ylabel('power')\n",
    "plt.grid(True)\n",
    "plt.savefig(filename2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "minus-boards",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
